﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using ClosedXML.Excel;

namespace MMS
{
    class MyExcel
    {
        public string Path { get; set; }
        public string Sheet_name { get; set; }
        XLWorkbook xml_file { get; set; }
        IXLWorksheet Sheet { get; set; }

        /// <summary>
        /// 实例化一个Excel文件操作对象
        /// 找不到文件会创建一个文件
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="sheet_name">工作表名</param>
        public MyExcel(string path,string sheet_name)
        {
            Console.WriteLine("OK");
            this.Sheet_name = sheet_name;
            this.Path = path;
            try
            {
                this.xml_file = new XLWorkbook(this.Path);
            }
            catch (IOException e)
            {
                Console.WriteLine("没有文件，创建文件");
                this.xml_file = new XLWorkbook();
                this.xml_file.AddWorksheet(this.Sheet_name);
                this.xml_file.SaveAs(this.Path);
            }
            this.Sheet = xml_file.Worksheet(this.Sheet_name);
        }
        ~MyExcel()
        {
            
        }
        /// <summary>
        /// 读写Excel文件一条数据
        /// 传入value改写Excel
        /// 不传value读取Excel
        /// </summary>
        /// <param name="row">Execl表的行</param>
        /// <param name="column">Execl表的列</param>
        /// <param name="value">要改写Excel的值</param>
        /// <returns>返回值：string</returns>
        public string ReadWrite_OnlyValue(int row, int column, string value = null)
        {
            
            //Console.WriteLine(0);
            if (value == null)
            {
                return this.Sheet.Cell(row, column).Value.ToString();
            }
            else
            {
                this.Sheet.Cell(row, column).Value = value;
                this.xml_file.Save();
                return "写入成功！";
            }
        }
        /// <summary>
        /// 读写Excel文件一行数据
        /// 传入value改写Excel
        /// 不传value读取Excel
        /// </summary>
        /// <param name="row">行</param>
        /// <param name="value">写入的值</param>
        /// <returns>返回值：List<string></returns>
        public List<string> ReadWrite_RowValue(int row, List<string> value = null)
        {
            List<string> Myreturn = new List<string>();
            int column = 1;
            while (true)
            {
                
                if (value == null)
                {
                    if (this.Sheet.Cell(row, column).Value.ToString() == "")
                    {
                        break;
                    }
                    Myreturn.Add( this.Sheet.Cell(row, column).Value.ToString());
                    
                }
                else
                {
                    if (column > value.Count)
                    {
                        break;
                    }
                    this.Sheet.Cell(row, column).Value = value[column-1];
                    
                }
                column++;
            }
            this.xml_file.Save();
            return Myreturn;
        }
        /// <summary>
        /// 读写Excel文件一列数据
        /// 传入value改写Excel
        /// 不传value读取Excel
        /// </summary>
        /// <param name="column">列</param>
        /// <param name="value">写入的值</param>
        /// <returns>返回值：List<string></returns>
        public List<string> ReadWrite_ColumnValue(int column, List<string> value = null)
        {
            List<string> Myreturn = new List<string>();
            int row = 1;
            while (true)
            {
                
                if (value == null)
                {
                    if (this.Sheet.Cell(row, column).Value.ToString() == "")
                    {
                        break;
                    }
                    Myreturn.Add(this.Sheet.Cell(row, column).Value.ToString());

                }
                else
                {
                    if (row > value.Count)
                    {
                        break;
                    }
                    this.Sheet.Cell(row, column).Value = value[row-1];
                    this.xml_file.Save();
                    //Console.WriteLine("value" + value[row - 1]);
                }
                row++;
            }
            return Myreturn;
        }

        public void RowAdd(int row,string value)
        {

        }
        /// <summary>
        /// 指定列追加
        /// </summary>
        /// <param name="column"></param>
        /// <param name="value"></param>
        public void ColumnAdd(int column, string value)
        {
            int row = 1;
            while (true)
            {
                if (this.Sheet.Cell(row, column).Value.ToString() == "")
                {
                    this.Sheet.Cell(row, column).Value = value;
                    this.xml_file.Save();
                    break;
                }               
                row++;
            }
        }
        /// <summary>
        /// 获取行或列元素个数
        /// </summary>
        /// <param name="number">行列数</param>
        /// <param name="type">行("row")或是列("column")，</param>
        /// <returns></returns>
        public int Count(int number,string type)
        {
            
            int count=1;
            if (type == "row")
            {
                while (this.Sheet.Cell(number, count).Value.ToString() != "")
                {
                    count++;
                }
            }
            else if(type=="column")
            {
                while (this.Sheet.Cell(count, number).Value.ToString() != "")
                {
                    count++;
                }
            }
            return count-1;
        }

        /// <summary>
        /// 清空Excel文件
        /// </summary>
        public void Close()
        {
            //int row = 1;
            //int column = 1;
            //while (true)
            //{
            //    while(true)
            //}
        }
    }
}
